﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.Net.Mail;
using System.IO;
using eOfficeDevManager;


namespace eOfficeTrayApp
{
   
    class SheduledReportForwarder
    {
        //mail
        MailLogFile _logFile = new MailLogFile();

        string _connectionString;

        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        public bool Run()
        {
            try
            {

                //mail
                _logFile.MailWriter("Running...");

                //1. Select all record from ReportSheduler where lastSenddate != current date
                //2. Start a loop to process each record
                //3. ForLOOP
                //    {
                //1. chcek the sheduleType 
                //Case Daily
                //{
                //1. Creare an instance of ReportGen class
                //2. set its properties. ConnectionString, ReportName, Template,Condition SQL etc by retriving 
                //from shedule record
                //3. Generate report with ReportName_Date as name
                //4. SendReport using email or FTP
                //5. Update last send date
                //}
                //Case Weekly
                //{
                //1. Check current day matched with Day settings of the shedule
                //2. If so then generate report as suggested above
                //3. SendReport using email or FTP
                //4. Update last send date
                //}
                //Case Monthly
                //{
                //}
                //    }   

                //_connectionString = "Data Source=PERSONAL-B1FAA2\\SQLEXPRESS;Initial Catalog=eOffice;User Id=sa;Password=bmjo";
                SqlConnection _sqlcon = new SqlConnection(_connectionString);
                SqlCommand _sqlcmd = new SqlCommand();
                _sqlcmd.Connection = _sqlcon;
                _sqlcon.Open();

                //mail
                _logFile.MailWriter("SqlConnection open...");

                _sqlcmd.CommandText = "select * from ReportScheduler where LastSendDate !='" + DateTime.Today + "'";
                SqlDataReader _sqldr = _sqlcmd.ExecuteReader();

                //mail
                _logFile.MailWriter("Last Mail date..." + _sqlcmd.CommandText);

                while (_sqldr.Read())
                {
                    //mail
                    _logFile.MailWriter("Mail will send today...");

                    string _strSchdType = _sqldr["ScheduleType"].ToString();
                    DateTime _dtLastSnd = Convert.ToDateTime(_sqldr["LastSendDate"].ToString());

                    //mail
                    _logFile.MailWriter("ScheduleType : " + _strSchdType);

                    switch (_strSchdType.ToLower())
                    {
                        case "daily":
                            {
                                //mail
                                _logFile.MailWriter("Daily");

                                ReportGen _rptGen = new ReportGen();
                                _rptGen.ConnectionString = _connectionString;

                                //mail
                                _logFile.MailWriter("connection string" + _connectionString);

                                _rptGen.ReportTemplate = _sqldr["ReportTemplate"].ToString();
                                _rptGen.ReportConditionSql = _sqldr["SelectionQuery"].ToString();
                                _rptGen.ReportType = _sqldr["ReportType"].ToString();
                                string[] _strDestination = _sqldr["Destination"].ToString().Split(',');

                                //mail
                                _logFile.MailWriter("ReportType : " + _sqldr["ReportType"].ToString());


                                if (_rptGen.GenerateReport())
                                {
                                    //mail
                                    _logFile.MailWriter("Report Generated");

                                    if (SendReportEmail(_sqldr["SourceID"].ToString(), _strDestination, _sqldr["Server"].ToString(), Convert.ToInt32(_sqldr["Port"].ToString()), _sqldr["UserName"].ToString(), _sqldr["Password"].ToString(), "Report", _rptGen.ReportOutputFileName + ".xls"))
                                    {
                                        //mail
                                        _logFile.MailWriter("Mail sent");


                                        //Report Sent                                    
                                        SendDateUpdate(int.Parse(_sqldr["SchedulerID"].ToString()));
                                    }
                                }

                            }
                            break;
                        case "weekly":
                            {
                                if (DateTime.Today.DayOfWeek == _dtLastSnd.DayOfWeek)
                                {
                                    ReportGen _rptGen = new ReportGen();
                                    _rptGen.ConnectionString = _connectionString;
                                    _rptGen.ReportOutputFileName = _sqldr["ReportTemplate"].ToString() + DateTime.Today.ToString();
                                    _rptGen.ReportTemplate = _sqldr["ReportTemplate"].ToString();
                                    _rptGen.ReportConditionSql = _sqldr["SelectionQuery"].ToString();
                                    string[] _strDestination = _sqldr["Destination"].ToString().Split(',');

                                    if (_rptGen.GenerateReport())
                                    {
                                        if (SendReportEmail(_sqldr["SourceID"].ToString(), _strDestination, _sqldr["Server"].ToString(), Convert.ToInt32(_sqldr["Port"].ToString()), _sqldr["UserName"].ToString(), _sqldr["Password"].ToString(), "Report", ""))
                                        {
                                            //Report Sent
                                            SendDateUpdate(int.Parse(_sqldr["SchedulerID"].ToString()));

                                            //mail
                                            _logFile.MailWriter("Date updated");

                                        }
                                    }
                                }
                            }
                            break;
                        case "monthly":
                            {
                                DateTime _dtToday = DateTime.Today;
                                if (_dtToday.Day == _dtLastSnd.Day)
                                {
                                    ReportGen _rptGen = new ReportGen();
                                    _rptGen.ConnectionString = _connectionString;
                                    _rptGen.ReportTemplate = _sqldr["ReportTemplate"].ToString();
                                    _rptGen.ReportConditionSql = _sqldr["SelectionQuery"].ToString();
                                    _rptGen.ReportType = _sqldr["ReportType"].ToString();
                                    string[] _strDestination = _sqldr["Destination"].ToString().Split(',');

                                    if (_rptGen.GenerateReport())
                                    {
                                        if (SendReportEmail(_sqldr["SourceID"].ToString(), _strDestination, _sqldr["Server"].ToString(), Convert.ToInt32(_sqldr["Port"].ToString()), _sqldr["UserName"].ToString(), _sqldr["Password"].ToString(), "Report", _rptGen.ReportOutputFileName.Remove(0, 6) + ".xls"))
                                        {
                                            //Report Sent                                    
                                            SendDateUpdate(int.Parse(_sqldr["SchedulerID"].ToString()));
                                        }
                                    }
                                }
                            }
                            break;
                    }
                }
                _sqlcon.Close();

                return true;
            }
            catch (Exception ex)
            {
                //mail
                _logFile.MailWriter("Run false : " + ex.Message);
                
                return false;
            }
        }

        private void SendDateUpdate(int _schId)
        {
            try
            {
                //mail
                _logFile.MailWriter("SendDateUpdate");

                SqlConnection _sqlcon = new SqlConnection(_connectionString);
                SqlCommand _sqlcmd = new SqlCommand();
                _sqlcmd.Connection = _sqlcon;
                _sqlcon.Open();
                _sqlcmd.CommandText = "Update ReportScheduler set LastSendDate = '" + DateTime.Today + "' where SchedulerID=" + _schId;
                _sqlcmd.ExecuteNonQuery();
                _sqlcon.Close();
            }
            catch (Exception ex)
            {
                //mail
                _logFile.MailWriter("SendDate update failed . " + ex.Message);
                
            }
        }

        bool SendReportEmail(string fromAddress, string[] toAdreses, string server, int port, string userName, string password, string subject, string attachment)
        {
            try
            {
                //mail
                _logFile.MailWriter("SendReportEmail ");
                

                MailMessage mail = new MailMessage();

                //set the addresses
                mail.From = new MailAddress(fromAddress);

                foreach(string strTo in toAdreses)
                    mail.To.Add(new MailAddress(strTo));

                //set the content
                mail.Subject = subject;
                mail.IsBodyHtml = true;
                mail.BodyEncoding = Encoding.UTF8;
                mail.Attachments.Add(new Attachment(attachment));

                //send the message
                SmtpClient smClient = new SmtpClient();
                smClient.Host = "smtp.gmail.com";
                smClient.Credentials = new NetworkCredential(userName, password);
                smClient.EnableSsl = true;

                smClient.Send(mail);
                return true;
            }
            catch(Exception ex)
            {
                //mail
                _logFile.MailWriter("Mail sending failed . " + ex.Message);
                
                return false;
            }
        }

    }

    class ReportGen
    {
      #region Properties
        string _connectionString;

        public string ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }
        string _reportTemplate;

        public string ReportTemplate
        {
            get { return _reportTemplate; }
            set { _reportTemplate = value; }
        }
        string _reportOutputFileName;

        public string ReportOutputFileName
        {
            get { return _reportOutputFileName; }
            set { _reportOutputFileName = value; }
        }
        string _reportConditionSql;

        public string ReportConditionSql
        {
            get { return _reportConditionSql; }
            set { _reportConditionSql = value; }
        }
        string _reportType;

        public string ReportType
        {
            get { return _reportType; }
            set { _reportType = value; }
        }

        DataSet _dsReports;

        public DataSet DsReports
        {
            get { return _dsReports; }
            set { _dsReports = value; }
        }
        #endregion Properties

        MailLogFile _logFile = new MailLogFile();
        EbExcelAuto exelAttnReport = new EbExcelAuto();
       
        SqlConnection _sqlcon;
        SqlCommand _sqlcmd;

        /// <summary>
        /// Generates the excell report. It expects the above given properties are set 
        /// 1. ReportType
        /// 2. ReportConditionSql
        /// 3. ReportOutputFileName
        /// 4. ReportTemplate
        /// 5. ConnectionString
        /// </summary>
        /// <returns></returns>
        public bool GenerateReport()
        {
            try
            {
                //mail
                _logFile.MailWriter("GnerateReport");


                //1. Chcek report type
                //2. Call corresponding functions to generate reports
                _dsReports = new DataSet();
                switch (_reportType.ToLower())
                {
                    case "daily":
                        CreateDailyReport();
                        break;
                    case "attendance":
                        CreateAttendanceReport();
                        break;
                    case "inout":
                        CreateInoutstatusReport();
                        break;
                    case "lateness":
                        CreateLatenessReport();
                        break;
                    case "presence":
                        CreatePresenceReport();
                        break;
                    case "absence":
                        CreateAbsenceReport();
                        break;
                    case "overtime":
                        CreateOvertimeReport();
                        break;
                    case "leave":
                        CreateLeaveReport();
                        break;
                }

                //After generate reports, it fill into corresponding excel file
                if(_dsReports.Tables[0].Rows.Count>0)
                    FillDatasetToExcel(_dsReports);
                return true;
            }
            catch (Exception ex)
            {
                //mails
                _logFile.MailWriter("Generate report failed." + ex.Message);                
                return false;                
            }
        }

        bool GenerateReport(string outputFileName, string templateFile)
        {
            return true;
        }

        //Reports :-
        public void CreateDailyReport()
        {
            try
            {
                //mail
                _logFile.MailWriter("CreateDailyReport");


                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand();
                _sqlcmd.Connection = _sqlcon;
                _sqlcmd.Parameters.Clear();
                _sqlcmd.CommandText = "DailyTransactionReport";
                _sqlcmd.CommandType = CommandType.StoredProcedure;

                //mail
                _logFile.MailWriter("DAte: " + DateTime.Today);


                SqlParameter param = _sqlcmd.Parameters.AddWithValue("@date", DateTime.Today);
                _sqlcmd.Parameters.AddWithValue("@brid", null);
                _sqlcmd.Parameters.AddWithValue("@deptid", null);
                _sqlcon.Open();
                _sqlcmd.ExecuteNonQuery();
                _sqlcon.Close();
                _sqlcon.Open();
                _sqlcmd.CommandText = "DailyTransaction_RPT";
                _sqlcmd.CommandType = CommandType.StoredProcedure;
                _sqlcmd.Parameters.Clear();
                _sqlcmd.Parameters.AddWithValue("@orderby", '1');                
                _sqlcmd.Parameters.AddWithValue("@groupby", '0');
                _sqlcmd.ExecuteNonQuery();
                SqlDataAdapter sqlAdap = new SqlDataAdapter(_sqlcmd);
                sqlAdap.Fill(_dsReports);

                 //mail
                _logFile.MailWriter("Report Rows Count : "+ _dsReports.Tables[0].Rows.Count);

                _sqlcon.Close();
                
            }
            catch (Exception ex)
            {
                //mail
                _logFile.MailWriter("Daily report creation failed . " + ex.Message);
                
            }
        }

        public void CreateAttendanceReport()
        {
            try
            {
                string strWhere = "";
                string strStartDate = DateTime.Today.ToString("MM/dd/yyyy");
                string strEndDate = DateTime.Today.ToString("MM/dd/yyyy");

                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand();
                _sqlcmd.Connection = _sqlcon;            
                strWhere = "Convert(varchar,AttnDate,101) >= '" + strStartDate + "' and AttnDate<='" + strEndDate + "'";                   
                _sqlcon.Open();
                _sqlcmd.CommandText = "SELECT     EmpFirstName +' '+ EmpLastName AS Name, ShName AS Shift, AttnDate AS Date, PresentText AS EmpStatus, Description AS DayStatus,"
                         + "dtHours AS [HoursWorked], dtMinutes AS [Worked(Min)], [OverTime(Hrs:Min)],"
                         + "[Lateness(Hrs:Min)], dtFirstInTime AS FirstInTime, dtLastOutTime AS LastOutTime"
                         + " FROM AttendanceReportView ";

                if (strWhere != "")
                {
                    strWhere = "where " + strWhere;
                    _sqlcmd.CommandText += strWhere;
                }
                _sqlcmd.CommandType = CommandType.Text;
                _sqlcmd.Parameters.Clear();
                _sqlcmd.ExecuteNonQuery();
                SqlDataAdapter sqlAdap = new SqlDataAdapter(_sqlcmd);
                sqlAdap.Fill(_dsReports);            
                _sqlcon.Close();
            }
            catch (Exception ex)
            {
            }
        }

        public void CreateInoutstatusReport()
        {
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand();
                _sqlcmd.Connection = _sqlcon;
                string strWhere = "";
                string strStartDate = DateTime.Today.ToString("MM/dd/yyyy");
                strWhere = strWhere + "DATEPART(dy, Convert(varchar,trsTimeStamp,101))= DATEPART(dy, '" + strStartDate + "') ";
                _sqlcon.Open();
                _sqlcmd.CommandText = "SELECT     EmpFirstName + ' ' + EmpLastName as Name, trsTimeStamp as Time,INOUTStr as INOUT, DepName as Department, BrName as Branch, EmpId as EmpID "
                                      + "FROM         dbo.InoutStatusView ";
                if (strWhere != "")
                {
                    strWhere = " where " + strWhere;
                    _sqlcmd.CommandText += strWhere;
                    _sqlcmd.CommandText += " order by EmpId";
                }
                _sqlcmd.CommandType = CommandType.Text;
                _sqlcmd.Parameters.Clear();
                _sqlcmd.ExecuteNonQuery();
                SqlDataAdapter sqlAdap = new SqlDataAdapter(_sqlcmd);
                sqlAdap.Fill(_dsReports);
                _sqlcon.Close();
            }
            catch (Exception ex)
            {
            }
        }

        public void CreateLatenessReport()
        {
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand();
                _sqlcmd.Connection = _sqlcon;
                string strWhere = "";
                string strStartDate = DateTime.Today.ToString("MM/dd/yyyy");
                string strEndDate = DateTime.Today.ToString("MM/dd/yyyy");
                strWhere = "Date >= '" + strStartDate.ToString() + "' and Date<='" + strEndDate.ToString() + "'";
                _sqlcon.Open();
                _sqlcmd.CommandText = "SELECT     EmpID, FirstName + ' ' + LastName as Name,[Date], ShiftName,[Lateness(Hrs:Min)],StartTime,EndTime, Branch, Department"
                                 + " FROM LatenessView ";

                if (strWhere != "")
                {
                    strWhere = " where " + strWhere;
                    _sqlcmd.CommandText += strWhere;
                    _sqlcmd.CommandText += " order by [Date]";

                }
                _sqlcmd.CommandType = CommandType.Text;
                _sqlcmd.Parameters.Clear();
                _sqlcmd.ExecuteNonQuery();
                SqlDataAdapter sqlAdap = new SqlDataAdapter(_sqlcmd);
                sqlAdap.Fill(_dsReports);
                _sqlcon.Close();
            }
            catch (Exception ex)
            {
            }
        }

        public void CreatePresenceReport()
        {
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand();
                _sqlcmd.Connection = _sqlcon;
                string strWhere = "";
                string strStartDate = DateTime.Today.ToString("MM/dd/yyyy");
                string strEndDate = DateTime.Today.ToString("MM/dd/yyyy");
                strWhere = "AND AttnDate >= '" + strStartDate.ToString() + "' and AttnDate<='" + strEndDate.ToString() + "'";
                _sqlcon.Open();
                _sqlcmd.CommandText = "SELECT     EmpFirstName + ' ' + EmpLastName AS Name, ShName AS Shift, AttnDate AS Date,Description AS DayStatus,"
                          + "dtHours AS [HoursWorked], dtMinutes AS [Worked(Min)], DepName AS Department, BrName AS Branch, [Overtime(Hrs:Min)],"
                          + "[Lateness(Hrs:Min)], dtFirstInTime AS FirstInTime, dtLastOutTime AS LastOutTime"
                          + " FROM AttendanceReportView where Present = 1 ";
                if (strWhere != "")
                    _sqlcmd.CommandText += strWhere;
                _sqlcmd.CommandType = CommandType.Text;
                _sqlcmd.Parameters.Clear();
                _sqlcmd.ExecuteNonQuery();
                SqlDataAdapter sqlAdap = new SqlDataAdapter(_sqlcmd);
                sqlAdap.Fill(_dsReports);
                _sqlcon.Close();
            }
            catch (Exception ex)
            {
            }
        }

        public void CreateAbsenceReport()
        {
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand();
                _sqlcmd.Connection = _sqlcon;
                string strWhere = "";
                string strStartDate = DateTime.Today.ToString("MM/dd/yyyy");
                string strEndDate = DateTime.Today.ToString("MM/dd/yyyy");
                strWhere = "AND AttnDate >= '" + strStartDate.ToString() + "' and AttnDate<='" + strEndDate.ToString() + "'";
                _sqlcon.Open();
                _sqlcmd.CommandText = "SELECT  EmpFirstName + ' ' + EmpLastName AS Name, ShName AS Shift, AttnDate AS Date,Description AS DayStatus,"
                          + "DepName AS Department, BrName AS Branch"
                          + " FROM AttendanceReportView where Present = 0 ";
                if (strWhere != "")
                {
                   _sqlcmd.CommandText += strWhere;
                }
                _sqlcmd.CommandType = CommandType.Text;
                _sqlcmd.Parameters.Clear();
                _sqlcmd.ExecuteNonQuery();
                SqlDataAdapter sqlAdap = new SqlDataAdapter(_sqlcmd);
                sqlAdap.Fill(_dsReports);
                _sqlcon.Close();
            }
            catch (Exception ex)
            {
                
            }
        }

        public void CreateOvertimeReport()
        {
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand();
                _sqlcmd.Connection = _sqlcon;
                string strWhere = "";
                string strStartDate = DateTime.Today.ToString("MM/dd/yyyy");
                string strEndDate = DateTime.Today.ToString("MM/dd/yyyy");
                strWhere = "AND AttnDate >= '" + strStartDate.ToString() + "' and AttnDate<='" + strEndDate.ToString() + "'";
                _sqlcon.Open();
                _sqlcmd.CommandText = "SELECT     EmpFirstName + ' ' + EmpLastName AS Name, ShName AS Shift, AttnDate AS Date,[Overtime(Hrs:Min)], Description AS DayStatus,"
                          + "DepName AS Department, BrName AS Branch"
                          + " FROM AttendanceReportView where ((DATEPART(hh,[OverTime(Hrs:Min)]) * 60) + DATEPART(mi, [OverTime(Hrs:Min)])) > 0 ";

                if (strWhere != "")
                     _sqlcmd.CommandText += strWhere;
                _sqlcmd.CommandType = CommandType.Text;
                _sqlcmd.Parameters.Clear();
                _sqlcmd.ExecuteNonQuery();
                SqlDataAdapter sqlAdap = new SqlDataAdapter(_sqlcmd);
                sqlAdap.Fill(_dsReports);
                _sqlcon.Close();
            }
            catch (Exception ex)
            {
            }
        }

        public void CreateLeaveReport()
        {
            try
            {
                _sqlcon = new SqlConnection(_connectionString);
                _sqlcmd = new SqlCommand();
                _sqlcmd.Connection = _sqlcon;
                string strWhere = "";
                string strStartDate = DateTime.Today.ToString("MM/dd/yyyy");
                string strEndDate = DateTime.Today.ToString("MM/dd/yyyy");
                strWhere = "AND AttnDate >= '" + strStartDate.ToString() + "' and AttnDate<='" + strEndDate.ToString() + "'";
                _sqlcmd.CommandText = "SELECT     EmpFirstName + ' ' + EmpLastName AS Name, ShName AS Shift, AttnDate AS Date,Description AS DayStatus,"
                          + " DepName AS Department, BrName AS Branch, LtName as LeaveType, LRNote as Description "
                          + " FROM LeaveReportView where Present = 2 ";

                if (strWhere != "")
                {
                    _sqlcmd.CommandText += strWhere;
                }
                _sqlcon.Open();
                _sqlcmd.CommandType = CommandType.Text;
                _sqlcmd.Parameters.Clear();
                _sqlcmd.ExecuteNonQuery();
                SqlDataAdapter sqlAdap = new SqlDataAdapter(_sqlcmd);
                sqlAdap.Fill(_dsReports);
                _sqlcon.Close();
            }
            catch (Exception ex)
            {
            }
        }

        private void FillDatasetToExcel(DataSet dsExcel)
        {
            try
            {
                //mail
                _logFile.MailWriter("FillDatasetToExcel");

                
                exelAttnReport.Init();
                string _strTemplate = Directory.GetCurrentDirectory() + "//" + _reportTemplate;
                //mail
                _logFile.MailWriter("StringTemplate : " + _strTemplate);
                
                //string _strTemplate = Properties.Settings.Default.CurrentPath.ToString() + "//" + _reportTemplate;
                exelAttnReport.OpenExcelFile(_strTemplate);
                exelAttnReport.FillExcellSheetFromDataset("#START", dsExcel, "Date");
                //_reportOutputFileName = Directory.GetCurrentDirectory() + "\\";
                _reportOutputFileName = exelAttnReport.SaveAs(null);

                //mail
                _logFile.MailWriter("ReportOutput : " + _reportOutputFileName);
                
                exelAttnReport.Close();
            }
            catch (Exception ex)
            {
                //Filling to excel failed..
                //mail
                _logFile.MailWriter("Filling to excel failed. " + ex.Message);
                
            }
        }
    }

    class MailLogFile
    {
        public void MailWriter(string Log)
        {
            string strFileName = "MailLog.txt";
            StreamWriter sWriter = new StreamWriter(strFileName, true);
            DateTime dateTime = DateTime.Now;
            sWriter.WriteLine(dateTime + "\r\t" + Log + "\r\n");
            sWriter.Close();
            sWriter.Dispose();
        }
    }
    
}
